package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.SqlModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据实现层
 * 1.得到数据
 * 2.从数据库得
 * 3.链接数据库
 * 4.jdbc 怎么写？
 */
public class UserImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
//        1.创建sql语句
        String sql = "select * from user where " +
                "username='" + userModel.getUsername() + "' and  " +
                "password='" + userModel.getPassword() + "'";
//        2.调用JDBC
        List list = new ArrayList();
        ResultSet rs = JDBC.query(sql, list);
//        java.sql.ResultSet rs = stmt.executeQuery(sql);
        //5.遍历结果
        UserModel model = null;
        try {
            while (rs.next()) {
//                int id = rs.getInt(1);
//                String username = rs.getString(2);
//                String password = rs.getString(3);
//                int age = rs.getInt(4);
                model = new UserModel();
//                int id = rs.getInt("id");
//                String username = rs.getString("username");
//                model.setId(id);
//                model.setUsername(username);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return model;
    }

    @Override
    public UserModel findByUsername(UserModel userModel) {
        String sql = "select * from user where username =?";
        List list = new ArrayList();
        list.add(userModel.getUsername());
        ResultSet resultSet = JDBC.query(sql, list);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    /**
     * ？和拼接的区别？
     * 1.？表示参数 直接传递参数
     * 2.拼接  容易出现sql注入
     *
     * @param userModel
     * @return
     */
    @Override
    public int add(UserModel userModel) {
        String sql = "INSERT INTO `user` " +
                "(`address`, `create_time`, `email`, " +
                "`enable`, `password`, `phone`," +
                " `sex`, `update_time`, `username`, " +
                "`hobby`, `birthday`) VALUES " +
                "(?, now(), ?," +
                " ?, ?,?, " +
                "?, now(), ?," +
                "?, ?);\n";
        List list = new ArrayList<>();
        list.add(userModel.getAddress());
        list.add(userModel.getEmail());
        list.add(userModel.getEnable());
        list.add(userModel.getPassword());
        list.add(userModel.getPhone());
        list.add(11);
        list.add(userModel.getUsername());
        list.add("");
        list.add("2021-11-11");
        return JDBC.update(sql, list);
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set username=?,address=?,enable=? where id=?";
        List list = new ArrayList<>();
        list.add(userModel.getAddress());
        list.add(userModel.getEmail());
        list.add(userModel.getEnable());
        list.add(userModel.getPassword());
        list.add(userModel.getPhone());
        list.add(11);
        list.add(userModel.getUsername());
        list.add("");
        list.add("2021-11-11");
        list.add(userModel.getId());
        return JDBC.update(sql, list);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=?";
        List list = new ArrayList<>();
        list.add(userModel.getId());
        return JDBC.update(sql, list);
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "SELECT *, date_format(birthday,'%Y-%m-%d') temp  FROM user  where true";
//        select * from user where 1=1
//        isEmpty这个判断是否位空包括null和“”；
//        if(userModel.getSex()==null||"".equals(userModel.getSex()))
//        List listSql = new ArrayList();
//        if (!"".equals(userModel.getSex())) {
//            sql += " and  sex = ?";
//            listSql.add(userModel.getSex().trim());
//        }
//        if (!"".equals(userModel.getUsername())) {
//            sql += " and  username like ?";
//            listSql.add("%" + userModel.getUsername().trim() + "%");
//        }
//        if (userModel.getEnable() != -1) {
//            if (userModel.getEnable() == 1) {
//                sql += " and enable =1";
//            } else {
//                sql += " and isnull(enable)";
//            }
////            listSql.add(userModel.getEnable());
//        }
//        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
//            sql += " and birthday <=?";
//            listSql.add(userModel.getEnd().trim());
//        }
//        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
//            sql += " and birthday >=?";
//            listSql.add(userModel.getStart().trim());
//        }
//        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
//            sql += " and birthday  between ? and ?";
//            listSql.add(userModel.getStart().trim());
//            listSql.add(userModel.getEnd().trim());
//        }
//        if (!"".equals(userModel.getHobby())) {
//
//            String[] hobbys = userModel.getHobby().split(",");
//            sql += " and (";
//            for (int i = 0; i < hobbys.length; i++) {
////                sql += " and  hobby like '%" + hobbys[i] + "%'";
//                sql += (i == 0 ? "" : "or") + "  find_in_set('" + hobbys[i] + "',hobby)";
//            }
//            sql += ")";
////            if()
////            listSql.add("%" + userModel.getHobby().trim() + "%");
//        }
//
//        sql += " order by id desc ";
//        SqlModel sqlModel = getSql(userModel);
        Map map = getSql(userModel);
//        sql += sqlModel.getSql();
        sql += map.get("sql");
        sql += " limit " + (userModel.getPageNum() - 1) * userModel.getPageSize() + "," + userModel.getPageSize() + "";


        ResultSet resultSet = JDBC.query(sql, (List) map.get("list"));
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setEmail(resultSet.getString("email"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreatTime(resultSet.getString("create_time"));
                model.setEnable(resultSet.getInt("enable"));
                model.setBirthday(resultSet.getString("temp"));
                model.setHobby(resultSet.getString("hobby"));
                model.setAddress(resultSet.getString("address"));
                model.setSex(resultSet.getString("sex"));
//                ---------------
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "SELECT *, date_format(birthday,'%Y-%m-%d') temp  FROM user where id=?";
        List list = new ArrayList();
        list.add(userModel.getId());
        ResultSet resultSet = JDBC.query(sql, list);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setEmail(resultSet.getString("email"));
                model.setPhone(resultSet.getString("phone"));
                model.setCreatTime(resultSet.getString("create_time"));
                model.setEnable(resultSet.getInt("enable"));
                model.setBirthday(resultSet.getString("temp"));
                model.setHobby(resultSet.getString("hobby"));
                model.setAddress(resultSet.getString("address"));
                model.setSex(resultSet.getString("sex"));
//                ---------------
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where true";
//        List listSql = new ArrayList();
//        SqlModel sqlModel = getSql(userModel);
        Map map = getSql(userModel);
//        list和map的区别？
        ResultSet resultSet = JDBC.query(sql + map.get("sql"), (List) map.get("list"));
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int changeEnable(UserModel userModel) {
//        UserModel model=(UserModel) JDBC.query("select * from where id="+userModel.getId(),null);
//        String sql;
//        if(model.getEnable()==1){
        String sql = "update user set enable= if(enable=1,0,1) where id=" + userModel.getId();
//        }else {
//             sql = "update user set enable=1 where id=" + userModel.getId();
//        }
//        String sql = "update user set enable=1 where id=" + userModel.getId();
        List list = new ArrayList();
        return JDBC.update(sql, list);
    }

    /**
     * 将条件语句放到一个方法
     *
     * @param userModel
     * @return
     */
    private Map getSql(UserModel userModel) {
        List listSql = new ArrayList();
//        SqlModel sqlModel = new SqlModel();
        Map map = new HashMap();
        String sql = "";
        if (!"".equals(userModel.getSex())) {
            sql += " and  sex = ?";
            listSql.add(userModel.getSex().trim());
        }
        if (!"".equals(userModel.getUsername())) {
            sql += " and  username like ?";
            listSql.add("%" + userModel.getUsername().trim() + "%");
        }
        if (userModel.getEnable() != -1) {
            if (userModel.getEnable() == 1) {
                sql += " and enable =1";
            } else {
                sql += " and isnull(enable)";
            }
//            listSql.add(userModel.getEnable());
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday <=?";
            listSql.add(userModel.getEnd().trim());
        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql += " and birthday >=?";
            listSql.add(userModel.getStart().trim());
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql += " and birthday  between ? and ?";
            listSql.add(userModel.getStart().trim());
            listSql.add(userModel.getEnd().trim());
        }
        if (!"".equals(userModel.getHobby())) {

            String[] hobbys = userModel.getHobby().split(",");
            sql += " and (";
            for (int i = 0; i < hobbys.length; i++) {
//                sql += " and  hobby like '%" + hobbys[i] + "%'";
                sql += (i == 0 ? "" : "or") + "  find_in_set('" + hobbys[i] + "',hobby)";
            }
            sql += ")";
//            if()
//            listSql.add("%" + userModel.getHobby().trim() + "%");
        }

        sql += " order by id desc ";
        map.put("sql", sql);
        map.put("list", listSql);
//        sqlModel.setListSql(listSql);
        return map;
    }
//    public static void main(String[] args) {
//        UserModel userModel=new UserModel();
//        userModel.setUsername("supeng");
//        userModel.setPassword("aaaaaa");
//        new UserImpl().findByUsernameAndPassword(userModel);
//    }
}
